package CustomClassFile.yueli;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import CustomClassFile.CronJob.DateUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import weaver.conn.RecordSet;
import weaver.general.SplitPageParaBean;
import weaver.general.SplitPageUtil;
import weaver.general.StaticObj;
import weaver.general.Util;

public class WqcxSearch {
	public static final Log logger = LogFactory.getLog(WqcxSearch.class);
	
	
	public static JSONObject selectRecord(String userid,String date,String date2,String name,int pageNo,int pageSize) {
		if(date2==null||date2.isEmpty()) {
			date2=DateUtil.dateString(new Date(),"yyyy-MM-dd");
		}
		if(date==null||date.isEmpty()) {
			date=DateUtil.dateString(new Date(),"yyyy-MM-01");
		}
		String departmentid=getDepartmentids(userid);
		String sql="";
		if ("".equals(departmentid)) {//如果不是部门负责人
			 sql="select w.id,w.sqcard,w.sqdatetime,w.sqrid,h.lastname,w.reason ," + 
					" cardtext=CASE w.sqcard WHEN 'card1' THEN '第一次卡' WHEN 'card2' THEN '第二次卡' WHEN 'card3' THEN '第三次卡' WHEN 'card4' THEN '第四次卡'  END" + 
					" from uf_wqsq w INNER JOIN (select * from HrmResource where  id="+userid+") h on w.sqrid=h.id"
							+ " where CONVERT(DATE,w.sqrq,23)>='"+date+"'"+" and CONVERT(DATE,w.sqrq,23)<='"+date2+"'";
		}else {
		
			sql="select w.id,w.sqcard,w.sqdatetime,w.sqrid,h.lastname,w.reason ," + 
				" cardtext=CASE w.sqcard WHEN 'card1' THEN '第一次卡' WHEN 'card2' THEN '第二次卡' WHEN 'card3' THEN '第三次卡' WHEN 'card4' THEN '第四次卡'  END" + 
				" from uf_wqsq w INNER JOIN (select * from HrmResource where departmentid in("+departmentid+") or id="+userid+") h on w.sqrid=h.id"
						+ " where CONVERT(DATE,w.sqrq,23)>='"+date+"' and CONVERT(DATE,w.sqrq,23)<='"+date2+"' and h.lastname like concat('"+name+"','%')";
		}
		logger.info("查询外勤申请sql:"+sql);
		/*分页查询代码，标准代码*/
		SplitPageParaBean spp = new SplitPageParaBean();
		spp.setSqlFrom("("+sql+") t1");
		spp.setPrimaryKey("id");
		spp.setBackFields("*");
		spp.setSqlOrderBy("t1.id");	//排序字段
		spp.setSortWay(1);	//0 升序   1 降序
		SplitPageUtil spu = new SplitPageUtil();
		spu.setSpp(spp);
		RecordSet rs = spu.getCurrentPageRs(pageNo,pageSize);
		/*将查询结果按照 字段名作为key，字段值作为value 动态封装成json，标准代码*/
		int totalSize = spu.getRecordCount();
		JSONArray datas = new JSONArray();
		String[] columnNames = rs.getColumnName();
		while(rs.next()){
			JSONObject jsonObj = new JSONObject();
			for(String columnName : columnNames){
				jsonObj.put(columnName, Util.formatMultiLang(rs.getString(columnName)));
			}
			datas.add(jsonObj);
		}
		JSONObject resultObj = new JSONObject();
		resultObj.put("totalSize", totalSize);
		resultObj.put("datas", JSONArray.fromObject(datas));
		return resultObj;
	} 
	
	private static String getDepartmentids(String userid) {
		
		RecordSet rs=new RecordSet();
		
		String sql="select deptid from HrmDepartmentDefined where CONVERT(VARCHAR(30),directorIII)='" + userid+"'"+
				" UNION" + 
				" select deptid from HrmDepartmentDefined where CONVERT(VARCHAR(30),directorII)='"+userid+"' and (CONVERT(VARCHAR(30),directorIII)='' or directorIII is null)" + 
				" UNION" + 
				" select deptid from HrmDepartmentDefined where CONVERT(VARCHAR(30),directorI)='"+userid+"' and (CONVERT(VARCHAR(30),directorII)='' or directorII is null) and (CONVERT(VARCHAR(30),directorIII)='' or directorIII is null)" ;
		rs.execute(sql);
		String departmentids="";
		while(rs.next()) {
			departmentids+="'"+rs.getString("deptid")+"'"+",";
		}
		if (!"".equals(departmentids)) {
			departmentids=departmentids.substring(0, departmentids.length()-1);
		}
		return departmentids;
	}
	
	/**
	 * 	获取当前考勤期间的开始日期
	 * @return
	 */
	public static JSONObject getKaoqingqijian() {
		String beginDate=DateUtil.getCurrentDateTime("yyyy-MM-01");
		String term=DateUtil.getCurrentDateTime("yyyy-MM");//获取当前月份
		java.sql.Connection conn = null;
		try {
			weaver.interfaces.datasource.DataSource ds = (weaver.interfaces.datasource.DataSource) StaticObj
					.getServiceByFullname(("datasource.HRDB"), weaver.interfaces.datasource.DataSource.class);
			conn = ds.getConnection();
			String sql = "select *,convert(VARCHAR(10),begindate,21) as beginDate1 from aAttend_Periods where convert(VARCHAR(7),term,21)=?";
			PreparedStatement preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, term);
			ResultSet rs = preparedStatement.executeQuery();
			if (rs.next()) {
				beginDate=rs.getString("beginDate1");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		JSONObject jsonObj = new JSONObject();
		jsonObj.put("beginDate", beginDate);
		return jsonObj;
	}
}
